
CREATE PROCEDURE dbo.[Sp_Banned_Create]
	@BannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_Banned] 
	(
		banned_ip
	)
	VALUES
	(
		@BannedIp
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@BannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @BannedIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @BannedIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@BannedIp', '''' + @BannedIp + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_Banned] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Banned_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_Banned]
		WHERE [dbo].[Tbl_Banned].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Banned_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_Banned] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Banned_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		banned_ip
	FROM dbo.[Tbl_Banned] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@BannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @BannedIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @BannedIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@BannedIp', '''' + @BannedIp + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		banned_ip
		FROM [dbo].[Tbl_Banned] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@BannedIp nvarchar(50),
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @BannedIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @BannedIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@BannedIp', '''' + @BannedIp + '''');
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH Banned_PageSegment AS ( 
		SELECT
		id,
		banned_ip,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_Banned ' + @Condition + '
		)

		SELECT 
		id,
		banned_ip
		FROM Banned_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@BannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @BannedIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @BannedIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@BannedIp', '''' + @BannedIp + '''');
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_Banned '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH Banned_PageSegment AS ( 
		SELECT 
		id, 
		banned_ip,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_Banned
		)

		SELECT 
		id,
		banned_ip
		FROM Banned_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_Banned '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Banned_Save]
	@Id int,
	@BannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_Banned] SET
	banned_ip = @BannedIp
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Banned_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@BannedIp nvarchar(50),
	@NewBannedIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @BannedIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @BannedIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@BannedIp', '''' + @BannedIp + '''');
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_Banned] SET ';
		
		IF @NewBannedIp IS NULL
			SET @sql = @sql + ' banned_ip = NULL ';
		ELSE
			SET @sql = @sql + ' banned_ip = ' +  '''' + @NewBannedIp + '''';

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_Link_Create]
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_Link] 
	(
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged
	)
	VALUES
	(
		@LinkUrl,
		@LinkDescription,
		@CurrentRating,
		@TotalRatingVote,
		@TotalVisits,
		@LinkCategoryId,
		@Flagged
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkUrl', '''' + @LinkUrl + '''');

		IF @LinkDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkDescription', '''' + @LinkDescription + '''');

		IF @CurrentRating IS NULL
			SET @Condition = REPLACE(@Condition, '= @CurrentRating', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CurrentRating', CONVERT(nvarchar(50),@CurrentRating ));

		IF @TotalRatingVote IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalRatingVote', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalRatingVote', CONVERT(nvarchar(50),@TotalRatingVote ));

		IF @TotalVisits IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalVisits', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalVisits', CONVERT(nvarchar(50),@TotalVisits ));

		IF @LinkCategoryId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCategoryId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCategoryId', CONVERT(nvarchar(50),@LinkCategoryId ));

		IF @Flagged IS NULL
			SET @Condition = REPLACE(@Condition, '= @Flagged', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Flagged', CONVERT(nvarchar(50),@Flagged ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_Link] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Link_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_Link]
		WHERE [dbo].[Tbl_Link].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Link_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_Link] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Link_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged
	FROM dbo.[Tbl_Link] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkUrl', '''' + @LinkUrl + '''');

		IF @LinkDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkDescription', '''' + @LinkDescription + '''');

		IF @CurrentRating IS NULL
			SET @Condition = REPLACE(@Condition, '= @CurrentRating', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CurrentRating', CONVERT(nvarchar(50),@CurrentRating ));

		IF @TotalRatingVote IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalRatingVote', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalRatingVote', CONVERT(nvarchar(50),@TotalRatingVote ));

		IF @TotalVisits IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalVisits', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalVisits', CONVERT(nvarchar(50),@TotalVisits ));

		IF @LinkCategoryId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCategoryId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCategoryId', CONVERT(nvarchar(50),@LinkCategoryId ));

		IF @Flagged IS NULL
			SET @Condition = REPLACE(@Condition, '= @Flagged', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Flagged', CONVERT(nvarchar(50),@Flagged ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged
		FROM [dbo].[Tbl_Link] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkUrl', '''' + @LinkUrl + '''');

		IF @LinkDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkDescription', '''' + @LinkDescription + '''');

		IF @CurrentRating IS NULL
			SET @Condition = REPLACE(@Condition, '= @CurrentRating', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CurrentRating', CONVERT(nvarchar(50),@CurrentRating ));

		IF @TotalRatingVote IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalRatingVote', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalRatingVote', CONVERT(nvarchar(50),@TotalRatingVote ));

		IF @TotalVisits IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalVisits', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalVisits', CONVERT(nvarchar(50),@TotalVisits ));

		IF @LinkCategoryId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCategoryId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCategoryId', CONVERT(nvarchar(50),@LinkCategoryId ));

		IF @Flagged IS NULL
			SET @Condition = REPLACE(@Condition, '= @Flagged', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Flagged', CONVERT(nvarchar(50),@Flagged ));
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH Link_PageSegment AS ( 
		SELECT
		id,
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_Link ' + @Condition + '
		)

		SELECT 
		id,
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged
		FROM Link_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkUrl', '''' + @LinkUrl + '''');

		IF @LinkDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkDescription', '''' + @LinkDescription + '''');

		IF @CurrentRating IS NULL
			SET @Condition = REPLACE(@Condition, '= @CurrentRating', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CurrentRating', CONVERT(nvarchar(50),@CurrentRating ));

		IF @TotalRatingVote IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalRatingVote', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalRatingVote', CONVERT(nvarchar(50),@TotalRatingVote ));

		IF @TotalVisits IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalVisits', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalVisits', CONVERT(nvarchar(50),@TotalVisits ));

		IF @LinkCategoryId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCategoryId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCategoryId', CONVERT(nvarchar(50),@LinkCategoryId ));

		IF @Flagged IS NULL
			SET @Condition = REPLACE(@Condition, '= @Flagged', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Flagged', CONVERT(nvarchar(50),@Flagged ));
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_Link '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH Link_PageSegment AS ( 
		SELECT 
		id, 
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_Link
		)

		SELECT 
		id,
		link_url,
		link_description,
		current_rating,
		total_rating_vote,
		total_visits,
		link_category_id,
		flagged
		FROM Link_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_Link '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_Link_Save]
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_Link] SET
	link_url = @LinkUrl,
	link_description = @LinkDescription,
	current_rating = @CurrentRating,
	total_rating_vote = @TotalRatingVote,
	total_visits = @TotalVisits,
	link_category_id = @LinkCategoryId,
	flagged = @Flagged
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_Link_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkUrl nvarchar(500),
	@LinkDescription nvarchar(4000),
	@CurrentRating decimal(18,2),
	@TotalRatingVote int,
	@TotalVisits int,
	@LinkCategoryId int,
	@Flagged bit,
	@NewLinkUrl nvarchar(500),
	@NewLinkDescription nvarchar(4000),
	@NewCurrentRating decimal(18,2),
	@NewTotalRatingVote int,
	@NewTotalVisits int,
	@NewLinkCategoryId int,
	@NewFlagged bit
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkUrl', '''' + @LinkUrl + '''');

		IF @LinkDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkDescription', '''' + @LinkDescription + '''');

		IF @CurrentRating IS NULL
			SET @Condition = REPLACE(@Condition, '= @CurrentRating', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CurrentRating', CONVERT(nvarchar(50),@CurrentRating ));

		IF @TotalRatingVote IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalRatingVote', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalRatingVote', CONVERT(nvarchar(50),@TotalRatingVote ));

		IF @TotalVisits IS NULL
			SET @Condition = REPLACE(@Condition, '= @TotalVisits', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@TotalVisits', CONVERT(nvarchar(50),@TotalVisits ));

		IF @LinkCategoryId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCategoryId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCategoryId', CONVERT(nvarchar(50),@LinkCategoryId ));

		IF @Flagged IS NULL
			SET @Condition = REPLACE(@Condition, '= @Flagged', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Flagged', CONVERT(nvarchar(50),@Flagged ));
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_Link] SET ';
		
		IF @NewLinkUrl IS NULL
			SET @sql = @sql + ' link_url = NULL ';
		ELSE
			SET @sql = @sql + ' link_url = ' +  '''' + @NewLinkUrl + '''';

		IF @NewLinkDescription IS NULL
			SET @sql = @sql + ' ,link_description = NULL ';
		ELSE
			SET @sql = @sql + ' ,link_description = ' +  '''' + @NewLinkDescription + '''';

		IF @NewCurrentRating IS NULL
			SET @sql = @sql + ' ,current_rating = NULL ';
		ELSE
			SET @sql = @sql + ' ,current_rating = ' + CONVERT(nvarchar(50),@NewCurrentRating );

		IF @NewTotalRatingVote IS NULL
			SET @sql = @sql + ' ,total_rating_vote = NULL ';
		ELSE
			SET @sql = @sql + ' ,total_rating_vote = ' + CONVERT(nvarchar(50),@NewTotalRatingVote );

		IF @NewTotalVisits IS NULL
			SET @sql = @sql + ' ,total_visits = NULL ';
		ELSE
			SET @sql = @sql + ' ,total_visits = ' + CONVERT(nvarchar(50),@NewTotalVisits );

		IF @NewLinkCategoryId IS NULL
			SET @sql = @sql + ' ,link_category_id = NULL ';
		ELSE
			SET @sql = @sql + ' ,link_category_id = ' + CONVERT(nvarchar(50),@NewLinkCategoryId );

		IF @NewFlagged IS NULL
			SET @sql = @sql + ' ,flagged = NULL ';
		ELSE
			SET @sql = @sql + ' ,flagged = ' + CONVERT(nvarchar(50),@NewFlagged );

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_LinkCategory_Create]
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_LinkCategory] 
	(
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count
	)
	VALUES
	(
		@CategoryName,
		@CategoryDescription,
		@CategoryImageUrl,
		@CreateDate,
		@LinkCount
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CategoryName IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryName', '''' + @CategoryName + '''');

		IF @CategoryDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryDescription', '''' + @CategoryDescription + '''');

		IF @CategoryImageUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryImageUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryImageUrl', '''' + @CategoryImageUrl + '''');

		IF @CreateDate IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreateDate', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreateDate', '''' + CONVERT(nvarchar(50),@CreateDate )+ '''');

		IF @LinkCount IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCount', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCount', CONVERT(nvarchar(50),@LinkCount ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkCategory] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkCategory_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkCategory]
		WHERE [dbo].[Tbl_LinkCategory].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkCategory_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_LinkCategory] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkCategory_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count
	FROM dbo.[Tbl_LinkCategory] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CategoryName IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryName', '''' + @CategoryName + '''');

		IF @CategoryDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryDescription', '''' + @CategoryDescription + '''');

		IF @CategoryImageUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryImageUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryImageUrl', '''' + @CategoryImageUrl + '''');

		IF @CreateDate IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreateDate', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreateDate', '''' + CONVERT(nvarchar(50),@CreateDate )+ '''');

		IF @LinkCount IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCount', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCount', CONVERT(nvarchar(50),@LinkCount ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count
		FROM [dbo].[Tbl_LinkCategory] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CategoryName IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryName', '''' + @CategoryName + '''');

		IF @CategoryDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryDescription', '''' + @CategoryDescription + '''');

		IF @CategoryImageUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryImageUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryImageUrl', '''' + @CategoryImageUrl + '''');

		IF @CreateDate IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreateDate', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreateDate', '''' + CONVERT(nvarchar(50),@CreateDate )+ '''');

		IF @LinkCount IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCount', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCount', CONVERT(nvarchar(50),@LinkCount ));
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkCategory_PageSegment AS ( 
		SELECT
		id,
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkCategory ' + @Condition + '
		)

		SELECT 
		id,
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count
		FROM LinkCategory_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CategoryName IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryName', '''' + @CategoryName + '''');

		IF @CategoryDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryDescription', '''' + @CategoryDescription + '''');

		IF @CategoryImageUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryImageUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryImageUrl', '''' + @CategoryImageUrl + '''');

		IF @CreateDate IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreateDate', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreateDate', '''' + CONVERT(nvarchar(50),@CreateDate )+ '''');

		IF @LinkCount IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCount', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCount', CONVERT(nvarchar(50),@LinkCount ));
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkCategory '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkCategory_PageSegment AS ( 
		SELECT 
		id, 
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkCategory
		)

		SELECT 
		id,
		category_name,
		category_description,
		category_image_url,
		create_date,
		link_count
		FROM LinkCategory_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkCategory '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkCategory_Save]
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_LinkCategory] SET
	category_name = @CategoryName,
	category_description = @CategoryDescription,
	category_image_url = @CategoryImageUrl,
	create_date = @CreateDate,
	link_count = @LinkCount
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkCategory_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CategoryName nvarchar(250),
	@CategoryDescription nvarchar(4000),
	@CategoryImageUrl nvarchar(250),
	@CreateDate datetime,
	@LinkCount int,
	@NewCategoryName nvarchar(250),
	@NewCategoryDescription nvarchar(4000),
	@NewCategoryImageUrl nvarchar(250),
	@NewCreateDate datetime,
	@NewLinkCount int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CategoryName IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryName', '''' + @CategoryName + '''');

		IF @CategoryDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryDescription', '''' + @CategoryDescription + '''');

		IF @CategoryImageUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @CategoryImageUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CategoryImageUrl', '''' + @CategoryImageUrl + '''');

		IF @CreateDate IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreateDate', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreateDate', '''' + CONVERT(nvarchar(50),@CreateDate )+ '''');

		IF @LinkCount IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkCount', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkCount', CONVERT(nvarchar(50),@LinkCount ));
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_LinkCategory] SET ';
		
		IF @NewCategoryName IS NULL
			SET @sql = @sql + ' category_name = NULL ';
		ELSE
			SET @sql = @sql + ' category_name = ' +  '''' + @NewCategoryName + '''';

		IF @NewCategoryDescription IS NULL
			SET @sql = @sql + ' ,category_description = NULL ';
		ELSE
			SET @sql = @sql + ' ,category_description = ' +  '''' + @NewCategoryDescription + '''';

		IF @NewCategoryImageUrl IS NULL
			SET @sql = @sql + ' ,category_image_url = NULL ';
		ELSE
			SET @sql = @sql + ' ,category_image_url = ' +  '''' + @NewCategoryImageUrl + '''';

		IF @NewCreateDate IS NULL
			SET @sql = @sql + ' ,create_date = NULL ';
		ELSE
			SET @sql = @sql + ' ,create_date = ' + '''' + CONVERT(nvarchar(50),@NewCreateDate ) + '''';

		IF @NewLinkCount IS NULL
			SET @sql = @sql + ' ,link_count = NULL ';
		ELSE
			SET @sql = @sql + ' ,link_count = ' + CONVERT(nvarchar(50),@NewLinkCount );

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_LinkGroup_Create]
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_LinkGroup] 
	(
		creator_user_id,
		group_title,
		group_description,
		sort_order
	)
	VALUES
	(
		@CreatorUserId,
		@GroupTitle,
		@GroupDescription,
		@SortOrder
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CreatorUserId IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreatorUserId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreatorUserId', CONVERT(nvarchar(50),@CreatorUserId ));

		IF @GroupTitle IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupTitle', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupTitle', '''' + @GroupTitle + '''');

		IF @GroupDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupDescription', '''' + @GroupDescription + '''');

		IF @SortOrder IS NULL
			SET @Condition = REPLACE(@Condition, '= @SortOrder', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SortOrder', CONVERT(nvarchar(50),@SortOrder ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkGroup] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroup_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkGroup]
		WHERE [dbo].[Tbl_LinkGroup].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroup_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_LinkGroup] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroup_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		creator_user_id,
		group_title,
		group_description,
		sort_order
	FROM dbo.[Tbl_LinkGroup] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CreatorUserId IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreatorUserId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreatorUserId', CONVERT(nvarchar(50),@CreatorUserId ));

		IF @GroupTitle IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupTitle', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupTitle', '''' + @GroupTitle + '''');

		IF @GroupDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupDescription', '''' + @GroupDescription + '''');

		IF @SortOrder IS NULL
			SET @Condition = REPLACE(@Condition, '= @SortOrder', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SortOrder', CONVERT(nvarchar(50),@SortOrder ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		creator_user_id,
		group_title,
		group_description,
		sort_order
		FROM [dbo].[Tbl_LinkGroup] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CreatorUserId IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreatorUserId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreatorUserId', CONVERT(nvarchar(50),@CreatorUserId ));

		IF @GroupTitle IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupTitle', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupTitle', '''' + @GroupTitle + '''');

		IF @GroupDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupDescription', '''' + @GroupDescription + '''');

		IF @SortOrder IS NULL
			SET @Condition = REPLACE(@Condition, '= @SortOrder', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SortOrder', CONVERT(nvarchar(50),@SortOrder ));
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkGroup_PageSegment AS ( 
		SELECT
		id,
		creator_user_id,
		group_title,
		group_description,
		sort_order,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkGroup ' + @Condition + '
		)

		SELECT 
		id,
		creator_user_id,
		group_title,
		group_description,
		sort_order
		FROM LinkGroup_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CreatorUserId IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreatorUserId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreatorUserId', CONVERT(nvarchar(50),@CreatorUserId ));

		IF @GroupTitle IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupTitle', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupTitle', '''' + @GroupTitle + '''');

		IF @GroupDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupDescription', '''' + @GroupDescription + '''');

		IF @SortOrder IS NULL
			SET @Condition = REPLACE(@Condition, '= @SortOrder', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SortOrder', CONVERT(nvarchar(50),@SortOrder ));
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkGroup '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkGroup_PageSegment AS ( 
		SELECT 
		id, 
		creator_user_id,
		group_title,
		group_description,
		sort_order,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkGroup
		)

		SELECT 
		id,
		creator_user_id,
		group_title,
		group_description,
		sort_order
		FROM LinkGroup_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkGroup '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroup_Save]
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_LinkGroup] SET
	creator_user_id = @CreatorUserId,
	group_title = @GroupTitle,
	group_description = @GroupDescription,
	sort_order = @SortOrder
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroup_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@CreatorUserId int,
	@GroupTitle nvarchar(250),
	@GroupDescription nvarchar(4000),
	@SortOrder int,
	@NewCreatorUserId int,
	@NewGroupTitle nvarchar(250),
	@NewGroupDescription nvarchar(4000),
	@NewSortOrder int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @CreatorUserId IS NULL
			SET @Condition = REPLACE(@Condition, '= @CreatorUserId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@CreatorUserId', CONVERT(nvarchar(50),@CreatorUserId ));

		IF @GroupTitle IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupTitle', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupTitle', '''' + @GroupTitle + '''');

		IF @GroupDescription IS NULL
			SET @Condition = REPLACE(@Condition, '= @GroupDescription', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@GroupDescription', '''' + @GroupDescription + '''');

		IF @SortOrder IS NULL
			SET @Condition = REPLACE(@Condition, '= @SortOrder', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SortOrder', CONVERT(nvarchar(50),@SortOrder ));
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_LinkGroup] SET ';
		
		IF @NewCreatorUserId IS NULL
			SET @sql = @sql + ' creator_user_id = NULL ';
		ELSE
			SET @sql = @sql + ' creator_user_id = ' + CONVERT(nvarchar(50),@NewCreatorUserId );

		IF @NewGroupTitle IS NULL
			SET @sql = @sql + ' ,group_title = NULL ';
		ELSE
			SET @sql = @sql + ' ,group_title = ' +  '''' + @NewGroupTitle + '''';

		IF @NewGroupDescription IS NULL
			SET @sql = @sql + ' ,group_description = NULL ';
		ELSE
			SET @sql = @sql + ' ,group_description = ' +  '''' + @NewGroupDescription + '''';

		IF @NewSortOrder IS NULL
			SET @sql = @sql + ' ,sort_order = NULL ';
		ELSE
			SET @sql = @sql + ' ,sort_order = ' + CONVERT(nvarchar(50),@NewSortOrder );

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_LinkGroupAndLink_Create]
	@LinkGroupId int,
	@LinkId int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_LinkGroupAndLink] 
	(
		link_group_id,
		link_id
	)
	VALUES
	(
		@LinkGroupId,
		@LinkId
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkGroupId int,
	@LinkId int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkGroupId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkGroupId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkGroupId', CONVERT(nvarchar(50),@LinkGroupId ));

		IF @LinkId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkId', CONVERT(nvarchar(50),@LinkId ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkGroupAndLink] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroupAndLink_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkGroupAndLink]
		WHERE [dbo].[Tbl_LinkGroupAndLink].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroupAndLink_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_LinkGroupAndLink] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroupAndLink_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		link_group_id,
		link_id
	FROM dbo.[Tbl_LinkGroupAndLink] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkGroupId int,
	@LinkId int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkGroupId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkGroupId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkGroupId', CONVERT(nvarchar(50),@LinkGroupId ));

		IF @LinkId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkId', CONVERT(nvarchar(50),@LinkId ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		link_group_id,
		link_id
		FROM [dbo].[Tbl_LinkGroupAndLink] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@LinkGroupId int,
	@LinkId int,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkGroupId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkGroupId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkGroupId', CONVERT(nvarchar(50),@LinkGroupId ));

		IF @LinkId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkId', CONVERT(nvarchar(50),@LinkId ));
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkGroupAndLink_PageSegment AS ( 
		SELECT
		id,
		link_group_id,
		link_id,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkGroupAndLink ' + @Condition + '
		)

		SELECT 
		id,
		link_group_id,
		link_id
		FROM LinkGroupAndLink_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@LinkGroupId int,
	@LinkId int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkGroupId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkGroupId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkGroupId', CONVERT(nvarchar(50),@LinkGroupId ));

		IF @LinkId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkId', CONVERT(nvarchar(50),@LinkId ));
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkGroupAndLink '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkGroupAndLink_PageSegment AS ( 
		SELECT 
		id, 
		link_group_id,
		link_id,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkGroupAndLink
		)

		SELECT 
		id,
		link_group_id,
		link_id
		FROM LinkGroupAndLink_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkGroupAndLink '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkGroupAndLink_Save]
	@Id int,
	@LinkGroupId int,
	@LinkId int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_LinkGroupAndLink] SET
	link_group_id = @LinkGroupId,
	link_id = @LinkId
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkGroupAndLink_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@LinkGroupId int,
	@LinkId int,
	@NewLinkGroupId int,
	@NewLinkId int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @LinkGroupId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkGroupId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkGroupId', CONVERT(nvarchar(50),@LinkGroupId ));

		IF @LinkId IS NULL
			SET @Condition = REPLACE(@Condition, '= @LinkId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LinkId', CONVERT(nvarchar(50),@LinkId ));
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_LinkGroupAndLink] SET ';
		
		IF @NewLinkGroupId IS NULL
			SET @sql = @sql + ' link_group_id = NULL ';
		ELSE
			SET @sql = @sql + ' link_group_id = ' + CONVERT(nvarchar(50),@NewLinkGroupId );

		IF @NewLinkId IS NULL
			SET @sql = @sql + ' ,link_id = NULL ';
		ELSE
			SET @sql = @sql + ' ,link_id = ' + CONVERT(nvarchar(50),@NewLinkId );

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_LinkSubmission_Create]
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_LinkSubmission] 
	(
		submitted_link_url,
		submitter_ip
	)
	VALUES
	(
		@SubmittedLinkUrl,
		@SubmitterIp
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @SubmittedLinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmittedLinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmittedLinkUrl', '''' + @SubmittedLinkUrl + '''');

		IF @SubmitterIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmitterIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmitterIp', '''' + @SubmitterIp + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkSubmission] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkSubmission_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_LinkSubmission]
		WHERE [dbo].[Tbl_LinkSubmission].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkSubmission_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_LinkSubmission] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkSubmission_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		submitted_link_url,
		submitter_ip
	FROM dbo.[Tbl_LinkSubmission] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @SubmittedLinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmittedLinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmittedLinkUrl', '''' + @SubmittedLinkUrl + '''');

		IF @SubmitterIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmitterIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmitterIp', '''' + @SubmitterIp + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		submitted_link_url,
		submitter_ip
		FROM [dbo].[Tbl_LinkSubmission] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50),
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @SubmittedLinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmittedLinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmittedLinkUrl', '''' + @SubmittedLinkUrl + '''');

		IF @SubmitterIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmitterIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmitterIp', '''' + @SubmitterIp + '''');
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkSubmission_PageSegment AS ( 
		SELECT
		id,
		submitted_link_url,
		submitter_ip,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkSubmission ' + @Condition + '
		)

		SELECT 
		id,
		submitted_link_url,
		submitter_ip
		FROM LinkSubmission_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @SubmittedLinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmittedLinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmittedLinkUrl', '''' + @SubmittedLinkUrl + '''');

		IF @SubmitterIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmitterIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmitterIp', '''' + @SubmitterIp + '''');
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkSubmission '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH LinkSubmission_PageSegment AS ( 
		SELECT 
		id, 
		submitted_link_url,
		submitter_ip,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_LinkSubmission
		)

		SELECT 
		id,
		submitted_link_url,
		submitter_ip
		FROM LinkSubmission_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_LinkSubmission '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_LinkSubmission_Save]
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_LinkSubmission] SET
	submitted_link_url = @SubmittedLinkUrl,
	submitter_ip = @SubmitterIp
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_LinkSubmission_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@SubmittedLinkUrl nvarchar(500),
	@SubmitterIp nvarchar(50),
	@NewSubmittedLinkUrl nvarchar(500),
	@NewSubmitterIp nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @SubmittedLinkUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmittedLinkUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmittedLinkUrl', '''' + @SubmittedLinkUrl + '''');

		IF @SubmitterIp IS NULL
			SET @Condition = REPLACE(@Condition, '= @SubmitterIp', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SubmitterIp', '''' + @SubmitterIp + '''');
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_LinkSubmission] SET ';
		
		IF @NewSubmittedLinkUrl IS NULL
			SET @sql = @sql + ' submitted_link_url = NULL ';
		ELSE
			SET @sql = @sql + ' submitted_link_url = ' +  '''' + @NewSubmittedLinkUrl + '''';

		IF @NewSubmitterIp IS NULL
			SET @sql = @sql + ' ,submitter_ip = NULL ';
		ELSE
			SET @sql = @sql + ' ,submitter_ip = ' +  '''' + @NewSubmitterIp + '''';

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_SiteSettings_Create]
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_SiteSettings] 
	(
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url
	)
	VALUES
	(
		@AdminMailAddress,
		@SupportMailAddress,
		@SmtpServerAddress,
		@SmtpServerUsername,
		@SmtpServerPassword,
		@SmtpServerPort,
		@DefaultThemeName,
		@DefaultUserRoleId,
		@SuperAdminUsername,
		@SuperAdminPassword,
		@SiteName,
		@SiteBaseUrl
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @AdminMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @AdminMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@AdminMailAddress', '''' + @AdminMailAddress + '''');

		IF @SupportMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SupportMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SupportMailAddress', '''' + @SupportMailAddress + '''');

		IF @SmtpServerAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerAddress', '''' + @SmtpServerAddress + '''');

		IF @SmtpServerUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerUsername', '''' + @SmtpServerUsername + '''');

		IF @SmtpServerPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPassword', '''' + @SmtpServerPassword + '''');

		IF @SmtpServerPort IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPort', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPort', CONVERT(nvarchar(50),@SmtpServerPort ));

		IF @DefaultThemeName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultThemeName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultThemeName', '''' + @DefaultThemeName + '''');

		IF @DefaultUserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultUserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultUserRoleId', CONVERT(nvarchar(50),@DefaultUserRoleId ));

		IF @SuperAdminUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminUsername', '''' + @SuperAdminUsername + '''');

		IF @SuperAdminPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminPassword', '''' + @SuperAdminPassword + '''');

		IF @SiteName IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteName', '''' + @SiteName + '''');

		IF @SiteBaseUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteBaseUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteBaseUrl', '''' + @SiteBaseUrl + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_SiteSettings] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteSettings_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_SiteSettings]
		WHERE [dbo].[Tbl_SiteSettings].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteSettings_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_SiteSettings] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteSettings_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url
	FROM dbo.[Tbl_SiteSettings] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @AdminMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @AdminMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@AdminMailAddress', '''' + @AdminMailAddress + '''');

		IF @SupportMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SupportMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SupportMailAddress', '''' + @SupportMailAddress + '''');

		IF @SmtpServerAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerAddress', '''' + @SmtpServerAddress + '''');

		IF @SmtpServerUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerUsername', '''' + @SmtpServerUsername + '''');

		IF @SmtpServerPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPassword', '''' + @SmtpServerPassword + '''');

		IF @SmtpServerPort IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPort', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPort', CONVERT(nvarchar(50),@SmtpServerPort ));

		IF @DefaultThemeName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultThemeName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultThemeName', '''' + @DefaultThemeName + '''');

		IF @DefaultUserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultUserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultUserRoleId', CONVERT(nvarchar(50),@DefaultUserRoleId ));

		IF @SuperAdminUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminUsername', '''' + @SuperAdminUsername + '''');

		IF @SuperAdminPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminPassword', '''' + @SuperAdminPassword + '''');

		IF @SiteName IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteName', '''' + @SiteName + '''');

		IF @SiteBaseUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteBaseUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteBaseUrl', '''' + @SiteBaseUrl + '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url
		FROM [dbo].[Tbl_SiteSettings] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150),
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @AdminMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @AdminMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@AdminMailAddress', '''' + @AdminMailAddress + '''');

		IF @SupportMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SupportMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SupportMailAddress', '''' + @SupportMailAddress + '''');

		IF @SmtpServerAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerAddress', '''' + @SmtpServerAddress + '''');

		IF @SmtpServerUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerUsername', '''' + @SmtpServerUsername + '''');

		IF @SmtpServerPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPassword', '''' + @SmtpServerPassword + '''');

		IF @SmtpServerPort IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPort', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPort', CONVERT(nvarchar(50),@SmtpServerPort ));

		IF @DefaultThemeName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultThemeName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultThemeName', '''' + @DefaultThemeName + '''');

		IF @DefaultUserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultUserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultUserRoleId', CONVERT(nvarchar(50),@DefaultUserRoleId ));

		IF @SuperAdminUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminUsername', '''' + @SuperAdminUsername + '''');

		IF @SuperAdminPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminPassword', '''' + @SuperAdminPassword + '''');

		IF @SiteName IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteName', '''' + @SiteName + '''');

		IF @SiteBaseUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteBaseUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteBaseUrl', '''' + @SiteBaseUrl + '''');
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH SiteSettings_PageSegment AS ( 
		SELECT
		id,
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_SiteSettings ' + @Condition + '
		)

		SELECT 
		id,
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url
		FROM SiteSettings_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @AdminMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @AdminMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@AdminMailAddress', '''' + @AdminMailAddress + '''');

		IF @SupportMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SupportMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SupportMailAddress', '''' + @SupportMailAddress + '''');

		IF @SmtpServerAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerAddress', '''' + @SmtpServerAddress + '''');

		IF @SmtpServerUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerUsername', '''' + @SmtpServerUsername + '''');

		IF @SmtpServerPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPassword', '''' + @SmtpServerPassword + '''');

		IF @SmtpServerPort IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPort', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPort', CONVERT(nvarchar(50),@SmtpServerPort ));

		IF @DefaultThemeName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultThemeName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultThemeName', '''' + @DefaultThemeName + '''');

		IF @DefaultUserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultUserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultUserRoleId', CONVERT(nvarchar(50),@DefaultUserRoleId ));

		IF @SuperAdminUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminUsername', '''' + @SuperAdminUsername + '''');

		IF @SuperAdminPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminPassword', '''' + @SuperAdminPassword + '''');

		IF @SiteName IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteName', '''' + @SiteName + '''');

		IF @SiteBaseUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteBaseUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteBaseUrl', '''' + @SiteBaseUrl + '''');
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_SiteSettings '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH SiteSettings_PageSegment AS ( 
		SELECT 
		id, 
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_SiteSettings
		)

		SELECT 
		id,
		admin_mail_address,
		support_mail_address,
		smtp_server_address,
		smtp_server_username,
		smtp_server_password,
		smtp_server_port,
		default_theme_name,
		default_user_role_id,
		super_admin_username,
		super_admin_password,
		site_name,
		site_base_url
		FROM SiteSettings_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_SiteSettings '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteSettings_Save]
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_SiteSettings] SET
	admin_mail_address = @AdminMailAddress,
	support_mail_address = @SupportMailAddress,
	smtp_server_address = @SmtpServerAddress,
	smtp_server_username = @SmtpServerUsername,
	smtp_server_password = @SmtpServerPassword,
	smtp_server_port = @SmtpServerPort,
	default_theme_name = @DefaultThemeName,
	default_user_role_id = @DefaultUserRoleId,
	super_admin_username = @SuperAdminUsername,
	super_admin_password = @SuperAdminPassword,
	site_name = @SiteName,
	site_base_url = @SiteBaseUrl
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteSettings_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@AdminMailAddress nvarchar(150),
	@SupportMailAddress nvarchar(150),
	@SmtpServerAddress nvarchar(150),
	@SmtpServerUsername nvarchar(150),
	@SmtpServerPassword nvarchar(150),
	@SmtpServerPort int,
	@DefaultThemeName nvarchar(150),
	@DefaultUserRoleId int,
	@SuperAdminUsername nvarchar(150),
	@SuperAdminPassword nvarchar(150),
	@SiteName nvarchar(150),
	@SiteBaseUrl nvarchar(150),
	@NewAdminMailAddress nvarchar(150),
	@NewSupportMailAddress nvarchar(150),
	@NewSmtpServerAddress nvarchar(150),
	@NewSmtpServerUsername nvarchar(150),
	@NewSmtpServerPassword nvarchar(150),
	@NewSmtpServerPort int,
	@NewDefaultThemeName nvarchar(150),
	@NewDefaultUserRoleId int,
	@NewSuperAdminUsername nvarchar(150),
	@NewSuperAdminPassword nvarchar(150),
	@NewSiteName nvarchar(150),
	@NewSiteBaseUrl nvarchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @AdminMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @AdminMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@AdminMailAddress', '''' + @AdminMailAddress + '''');

		IF @SupportMailAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SupportMailAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SupportMailAddress', '''' + @SupportMailAddress + '''');

		IF @SmtpServerAddress IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerAddress', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerAddress', '''' + @SmtpServerAddress + '''');

		IF @SmtpServerUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerUsername', '''' + @SmtpServerUsername + '''');

		IF @SmtpServerPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPassword', '''' + @SmtpServerPassword + '''');

		IF @SmtpServerPort IS NULL
			SET @Condition = REPLACE(@Condition, '= @SmtpServerPort', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SmtpServerPort', CONVERT(nvarchar(50),@SmtpServerPort ));

		IF @DefaultThemeName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultThemeName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultThemeName', '''' + @DefaultThemeName + '''');

		IF @DefaultUserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @DefaultUserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DefaultUserRoleId', CONVERT(nvarchar(50),@DefaultUserRoleId ));

		IF @SuperAdminUsername IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminUsername', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminUsername', '''' + @SuperAdminUsername + '''');

		IF @SuperAdminPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @SuperAdminPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SuperAdminPassword', '''' + @SuperAdminPassword + '''');

		IF @SiteName IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteName', '''' + @SiteName + '''');

		IF @SiteBaseUrl IS NULL
			SET @Condition = REPLACE(@Condition, '= @SiteBaseUrl', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@SiteBaseUrl', '''' + @SiteBaseUrl + '''');
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_SiteSettings] SET ';
		
		IF @NewAdminMailAddress IS NULL
			SET @sql = @sql + ' admin_mail_address = NULL ';
		ELSE
			SET @sql = @sql + ' admin_mail_address = ' +  '''' + @NewAdminMailAddress + '''';

		IF @NewSupportMailAddress IS NULL
			SET @sql = @sql + ' ,support_mail_address = NULL ';
		ELSE
			SET @sql = @sql + ' ,support_mail_address = ' +  '''' + @NewSupportMailAddress + '''';

		IF @NewSmtpServerAddress IS NULL
			SET @sql = @sql + ' ,smtp_server_address = NULL ';
		ELSE
			SET @sql = @sql + ' ,smtp_server_address = ' +  '''' + @NewSmtpServerAddress + '''';

		IF @NewSmtpServerUsername IS NULL
			SET @sql = @sql + ' ,smtp_server_username = NULL ';
		ELSE
			SET @sql = @sql + ' ,smtp_server_username = ' +  '''' + @NewSmtpServerUsername + '''';

		IF @NewSmtpServerPassword IS NULL
			SET @sql = @sql + ' ,smtp_server_password = NULL ';
		ELSE
			SET @sql = @sql + ' ,smtp_server_password = ' +  '''' + @NewSmtpServerPassword + '''';

		IF @NewSmtpServerPort IS NULL
			SET @sql = @sql + ' ,smtp_server_port = NULL ';
		ELSE
			SET @sql = @sql + ' ,smtp_server_port = ' + CONVERT(nvarchar(50),@NewSmtpServerPort );

		IF @NewDefaultThemeName IS NULL
			SET @sql = @sql + ' ,default_theme_name = NULL ';
		ELSE
			SET @sql = @sql + ' ,default_theme_name = ' +  '''' + @NewDefaultThemeName + '''';

		IF @NewDefaultUserRoleId IS NULL
			SET @sql = @sql + ' ,default_user_role_id = NULL ';
		ELSE
			SET @sql = @sql + ' ,default_user_role_id = ' + CONVERT(nvarchar(50),@NewDefaultUserRoleId );

		IF @NewSuperAdminUsername IS NULL
			SET @sql = @sql + ' ,super_admin_username = NULL ';
		ELSE
			SET @sql = @sql + ' ,super_admin_username = ' +  '''' + @NewSuperAdminUsername + '''';

		IF @NewSuperAdminPassword IS NULL
			SET @sql = @sql + ' ,super_admin_password = NULL ';
		ELSE
			SET @sql = @sql + ' ,super_admin_password = ' +  '''' + @NewSuperAdminPassword + '''';

		IF @NewSiteName IS NULL
			SET @sql = @sql + ' ,site_name = NULL ';
		ELSE
			SET @sql = @sql + ' ,site_name = ' +  '''' + @NewSiteName + '''';

		IF @NewSiteBaseUrl IS NULL
			SET @sql = @sql + ' ,site_base_url = NULL ';
		ELSE
			SET @sql = @sql + ' ,site_base_url = ' +  '''' + @NewSiteBaseUrl + '''';

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_SiteUser_Create]
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_SiteUser] 
	(
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit
	)
	VALUES
	(
		@UserLoginUserName,
		@UserLoginPassword,
		@UserEmail,
		@DeliciousUserName,
		@DeliciousPassword,
		@UserRoleId,
		@LastVisit
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @UserLoginUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginUserName', '''' + @UserLoginUserName + '''');

		IF @UserLoginPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginPassword', '''' + @UserLoginPassword + '''');

		IF @UserEmail IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserEmail', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserEmail', '''' + @UserEmail + '''');

		IF @DeliciousUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousUserName', '''' + @DeliciousUserName + '''');

		IF @DeliciousPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousPassword', '''' + @DeliciousPassword + '''');

		IF @UserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserRoleId', CONVERT(nvarchar(50),@UserRoleId ));

		IF @LastVisit IS NULL
			SET @Condition = REPLACE(@Condition, '= @LastVisit', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LastVisit', '''' + CONVERT(nvarchar(50),@LastVisit )+ '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_SiteUser] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteUser_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_SiteUser]
		WHERE [dbo].[Tbl_SiteUser].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteUser_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_SiteUser] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteUser_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit
	FROM dbo.[Tbl_SiteUser] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @UserLoginUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginUserName', '''' + @UserLoginUserName + '''');

		IF @UserLoginPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginPassword', '''' + @UserLoginPassword + '''');

		IF @UserEmail IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserEmail', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserEmail', '''' + @UserEmail + '''');

		IF @DeliciousUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousUserName', '''' + @DeliciousUserName + '''');

		IF @DeliciousPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousPassword', '''' + @DeliciousPassword + '''');

		IF @UserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserRoleId', CONVERT(nvarchar(50),@UserRoleId ));

		IF @LastVisit IS NULL
			SET @Condition = REPLACE(@Condition, '= @LastVisit', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LastVisit', '''' + CONVERT(nvarchar(50),@LastVisit )+ '''');
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit
		FROM [dbo].[Tbl_SiteUser] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @UserLoginUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginUserName', '''' + @UserLoginUserName + '''');

		IF @UserLoginPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginPassword', '''' + @UserLoginPassword + '''');

		IF @UserEmail IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserEmail', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserEmail', '''' + @UserEmail + '''');

		IF @DeliciousUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousUserName', '''' + @DeliciousUserName + '''');

		IF @DeliciousPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousPassword', '''' + @DeliciousPassword + '''');

		IF @UserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserRoleId', CONVERT(nvarchar(50),@UserRoleId ));

		IF @LastVisit IS NULL
			SET @Condition = REPLACE(@Condition, '= @LastVisit', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LastVisit', '''' + CONVERT(nvarchar(50),@LastVisit )+ '''');
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH SiteUser_PageSegment AS ( 
		SELECT
		id,
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_SiteUser ' + @Condition + '
		)

		SELECT 
		id,
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit
		FROM SiteUser_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @UserLoginUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginUserName', '''' + @UserLoginUserName + '''');

		IF @UserLoginPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginPassword', '''' + @UserLoginPassword + '''');

		IF @UserEmail IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserEmail', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserEmail', '''' + @UserEmail + '''');

		IF @DeliciousUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousUserName', '''' + @DeliciousUserName + '''');

		IF @DeliciousPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousPassword', '''' + @DeliciousPassword + '''');

		IF @UserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserRoleId', CONVERT(nvarchar(50),@UserRoleId ));

		IF @LastVisit IS NULL
			SET @Condition = REPLACE(@Condition, '= @LastVisit', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LastVisit', '''' + CONVERT(nvarchar(50),@LastVisit )+ '''');
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_SiteUser '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH SiteUser_PageSegment AS ( 
		SELECT 
		id, 
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_SiteUser
		)

		SELECT 
		id,
		user_login_user_name,
		user_login_password,
		user_email,
		delicious_user_name,
		delicious_password,
		user_role_id,
		last_visit
		FROM SiteUser_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_SiteUser '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_SiteUser_Save]
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_SiteUser] SET
	user_login_user_name = @UserLoginUserName,
	user_login_password = @UserLoginPassword,
	user_email = @UserEmail,
	delicious_user_name = @DeliciousUserName,
	delicious_password = @DeliciousPassword,
	user_role_id = @UserRoleId,
	last_visit = @LastVisit
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_SiteUser_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@UserLoginUserName nvarchar(150),
	@UserLoginPassword nvarchar(150),
	@UserEmail nvarchar(150),
	@DeliciousUserName nvarchar(250),
	@DeliciousPassword nvarchar(250),
	@UserRoleId int,
	@LastVisit datetime,
	@NewUserLoginUserName nvarchar(150),
	@NewUserLoginPassword nvarchar(150),
	@NewUserEmail nvarchar(150),
	@NewDeliciousUserName nvarchar(250),
	@NewDeliciousPassword nvarchar(250),
	@NewUserRoleId int,
	@NewLastVisit datetime
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @UserLoginUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginUserName', '''' + @UserLoginUserName + '''');

		IF @UserLoginPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserLoginPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserLoginPassword', '''' + @UserLoginPassword + '''');

		IF @UserEmail IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserEmail', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserEmail', '''' + @UserEmail + '''');

		IF @DeliciousUserName IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousUserName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousUserName', '''' + @DeliciousUserName + '''');

		IF @DeliciousPassword IS NULL
			SET @Condition = REPLACE(@Condition, '= @DeliciousPassword', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@DeliciousPassword', '''' + @DeliciousPassword + '''');

		IF @UserRoleId IS NULL
			SET @Condition = REPLACE(@Condition, '= @UserRoleId', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@UserRoleId', CONVERT(nvarchar(50),@UserRoleId ));

		IF @LastVisit IS NULL
			SET @Condition = REPLACE(@Condition, '= @LastVisit', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@LastVisit', '''' + CONVERT(nvarchar(50),@LastVisit )+ '''');
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_SiteUser] SET ';
		
		IF @NewUserLoginUserName IS NULL
			SET @sql = @sql + ' user_login_user_name = NULL ';
		ELSE
			SET @sql = @sql + ' user_login_user_name = ' +  '''' + @NewUserLoginUserName + '''';

		IF @NewUserLoginPassword IS NULL
			SET @sql = @sql + ' ,user_login_password = NULL ';
		ELSE
			SET @sql = @sql + ' ,user_login_password = ' +  '''' + @NewUserLoginPassword + '''';

		IF @NewUserEmail IS NULL
			SET @sql = @sql + ' ,user_email = NULL ';
		ELSE
			SET @sql = @sql + ' ,user_email = ' +  '''' + @NewUserEmail + '''';

		IF @NewDeliciousUserName IS NULL
			SET @sql = @sql + ' ,delicious_user_name = NULL ';
		ELSE
			SET @sql = @sql + ' ,delicious_user_name = ' +  '''' + @NewDeliciousUserName + '''';

		IF @NewDeliciousPassword IS NULL
			SET @sql = @sql + ' ,delicious_password = NULL ';
		ELSE
			SET @sql = @sql + ' ,delicious_password = ' +  '''' + @NewDeliciousPassword + '''';

		IF @NewUserRoleId IS NULL
			SET @sql = @sql + ' ,user_role_id = NULL ';
		ELSE
			SET @sql = @sql + ' ,user_role_id = ' + CONVERT(nvarchar(50),@NewUserRoleId );

		IF @NewLastVisit IS NULL
			SET @sql = @sql + ' ,last_visit = NULL ';
		ELSE
			SET @sql = @sql + ' ,last_visit = ' + '''' + CONVERT(nvarchar(50),@NewLastVisit ) + '''';

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.[Sp_UserRole_Create]
	@RoleName nvarchar(150),
	@RoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.[Tbl_UserRole] 
	(
		role_name,
		role_level
	)
	VALUES
	(
		@RoleName,
		@RoleLevel
	)
	

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@IDENTITY
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_DeleteConditional]
	@Condition nvarchar(2000),
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @RoleName IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleName', '''' + @RoleName + '''');

		IF @RoleLevel IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleLevel', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleLevel', CONVERT(nvarchar(50),@RoleLevel ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_UserRole] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_UserRole_DeleteMultiple]
	@Ids nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	
		DECLARE @sql NVarChar(2000)
		SET @sql = 'DELETE FROM [dbo].[Tbl_UserRole]
		WHERE [dbo].[Tbl_UserRole].id IN
		(' 
			+ @Ids +
		')'
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_UserRole_DeleteOne]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM dbo.[Tbl_UserRole] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_UserRole_Load]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		id, 
		role_name,
		role_level
	FROM dbo.[Tbl_UserRole] 
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_LoadConditional]
	@Condition nvarchar(2000),
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @RoleName IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleName', '''' + @RoleName + '''');

		IF @RoleLevel IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleLevel', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleLevel', CONVERT(nvarchar(50),@RoleLevel ));
		
		DECLARE @sql NVarChar(2000)
		SET @sql = 'SELECT 
		id,
		role_name,
		role_level
		FROM [dbo].[Tbl_UserRole] ' + @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_LoadConditionalPaged]
	@Condition nvarchar(2000),
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int,
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @RoleName IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleName', '''' + @RoleName + '''');

		IF @RoleLevel IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleLevel', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleLevel', CONVERT(nvarchar(50),@RoleLevel ));
			
		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH UserRole_PageSegment AS ( 
		SELECT
		id,
		role_name,
		role_level,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_UserRole ' + @Condition + '
		)

		SELECT 
		id,
		role_name,
		role_level
		FROM UserRole_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND ( ' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ' ) - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_LoadConditionalPagedCount]
	@Condition nvarchar(2000),
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		
		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @RoleName IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleName', '''' + @RoleName + '''');

		IF @RoleLevel IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleLevel', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleLevel', CONVERT(nvarchar(50),@RoleLevel ));
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_UserRole '
		+ @Condition
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_LoadPaged]
	@OrderBy nvarchar(50),
	@StartRow int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
		SET @StartRow = @StartRow + 1;

		DECLARE @sql NVarChar(2000)
		SET @sql = ' WITH UserRole_PageSegment AS ( 
		SELECT 
		id, 
		role_name,
		role_level,
		ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + ' ) as RowIndex
		FROM Tbl_UserRole
		)

		SELECT 
		id,
		role_name,
		role_level
		FROM UserRole_PageSegment 
		WHERE RowIndex BETWEEN ' 
		+  CONVERT(nvarchar(20),@StartRow)  
		+ ' AND (' 
		+ CONVERT(nvarchar(20),@StartRow) + ' + ' + CONVERT(nvarchar(20),@PageSize)  
		+ ') - 1  '
		+ ' ORDER BY ' 
		+ CONVERT(nvarchar(50),@OrderBy);
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_LoadPagedCount]
	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
			
		DECLARE @sql NVarChar(2000)
		
		SET @sql = ' 
		SELECT COUNT(*)
		FROM Tbl_UserRole '
		
		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE dbo.[Sp_UserRole_Save]
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.[Tbl_UserRole] SET
	role_name = @RoleName,
	role_level = @RoleLevel
	WHERE id = @Id

	DECLARE @ReturnValue Int	
	SET @ReturnValue = @@ROWCOUNT
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Sp_UserRole_SaveConditional]
	@Condition nvarchar(2000),
	@Id int,
	@RoleName nvarchar(150),
	@RoleLevel int,
	@NewRoleName nvarchar(150),
	@NewRoleLevel int
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

		IF @Id IS NULL
			SET @Condition = REPLACE(@Condition, '@Id', '0');
		ELSE
			SET @Condition = REPLACE(@Condition, '@Id', CONVERT(nvarchar(50),@Id));

		IF @RoleName IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleName', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleName', '''' + @RoleName + '''');

		IF @RoleLevel IS NULL
			SET @Condition = REPLACE(@Condition, '= @RoleLevel', 'IS NULL');
		ELSE
			SET @Condition = REPLACE(@Condition, '@RoleLevel', CONVERT(nvarchar(50),@RoleLevel ));
				
		DECLARE @sql NVarChar(2000)
		SET @sql = 'UPDATE [dbo].[Tbl_UserRole] SET ';
		
		IF @NewRoleName IS NULL
			SET @sql = @sql + ' role_name = NULL ';
		ELSE
			SET @sql = @sql + ' role_name = ' +  '''' + @NewRoleName + '''';

		IF @NewRoleLevel IS NULL
			SET @sql = @sql + ' ,role_level = NULL ';
		ELSE
			SET @sql = @sql + ' ,role_level = ' + CONVERT(nvarchar(50),@NewRoleLevel );

		SET @sql = @sql + ' ' + @Condition

		EXEC sp_executesql @sql
		
		DECLARE @ReturnValue Int	
		SET @ReturnValue = @@ROWCOUNT
	
	COMMIT TRAN
	RETURN @ReturnValue
END
GO

------------------------------------------------------------------------------